//count products in each category
SELECT parent.cat_name, COUNT(kpulse_products.product_name)
FROM kpulse_categories AS node ,
kpulse_categories AS parent,
kpulse_products
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND node.cat_id = kpulse_products.product_cat_id
GROUP BY parent.cat_name
ORDER BY node.cat_lft;


//all product in cat and subcats --test version
SELECT kpulse_products.*
FROM kpulse_categories AS parent,  kpulse_categories AS node,
kpulse_products
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND parent.cat_link = "all/pnevmaticheskoe_oruzhie/" AND 
node.cat_id = kpulse_products.product_cat_id
ORDER BY parent.cat_lft;


//all cat tree
SELECT CONCAT( REPEAT('-', COUNT(parent.cat_name) - 1), node.cat_name) AS name
FROM kpulse_categories AS node,
kpulse_categories AS parent
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
GROUP BY node.cat_lft
ORDER BY node.cat_lft;

//backreference
SELECT parent.cat_link, parent.cat_name 
FROM kpulse_categories AS node,
kpulse_categories AS parent
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND node.cat_link = 'all/posuda/'
AND parent.cat_id > 1
ORDER BY parent.cat_lft;

//load menu with depth value
SELECT node.cat_name, node.cat_link, (COUNT(parent.cat_name) - 1) AS depth
FROM kpulse_categories AS node,
kpulse_categories AS parent
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
GROUP BY node.cat_link
ORDER BY node.cat_lft;

//show subcats
SELECT node.cat_name, node.cat_link, (COUNT(parent.cat_name) - (sub_tree.depth + 1)) AS depth
FROM kpulse_categories AS node,
	kpulse_categories AS parent,
	kpulse_categories AS sub_parent,
	(
		SELECT node.cat_name, (COUNT(parent.cat_name) - 1) AS depth
		FROM kpulse_categories AS node,
		kpulse_categories AS parent
		WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
		AND node.cat_link = 'all/'
		GROUP BY node.cat_name
		ORDER BY node.cat_lft
	)AS sub_tree
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
	AND node.cat_lft BETWEEN sub_parent.cat_lft AND sub_parent.cat_rgt
	AND sub_parent.cat_name = sub_tree.cat_name
GROUP BY node.cat_name
HAVING depth = 1
ORDER BY node.cat_lft;

//show parent directory
SELECT parent.cat_name
FROM kpulse_categories AS node,
kpulse_categories AS parent
WHERE node.cat_lft BETWEEN parent.cat_lft AND parent.cat_rgt
AND node.cat_name = 'Фонари'
ORDER BY parent.cat_lft DESC
LIMIT 1,1

